package mock.ab_sd.daointerface;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;

import mock.ab_sd.common.utility.ListPaging;
import mock.ab_sd.common.valueobject.GovernmentVO;
/**
 * 
 * @author HAI
 *
 */
public class GovernmentDAOImpl implements GovernmentDAO{
	/**
	 * 
	 * @param paging
	 * @return
	 * @throws Exception
	 */
	public ArrayList<GovernmentVO> getGovernmentList(ListPaging paging)
			throws Exception {
		// create an Array List governmentVO
		ArrayList<GovernmentVO> governmentList = new ArrayList<GovernmentVO>();
		// get connection
		Connection connect = ConnectionFactory.getConnect();
		// create query to call store procedure sp_ListGOR
		String query = "{call sp_ListGOR(?,?,?,?,?,?,?)}";
		// create CallableStatement object to prepare call
		CallableStatement cStatement = connect.prepareCall(query);
		// add filter
		cStatement.setString(1, paging.getFilter().toString());
		// order by
		cStatement.setString(2, paging.getOrderBy().toString());
		// order criteria
		cStatement.setString(3, paging.getOrderCriteria().toString());
		// page number
		cStatement.setInt(4, paging.getPageNo());
		// records per page
		cStatement.setInt(5, paging.getRecordPerPage());
		// get out parameter total page of records
		cStatement.registerOutParameter(6, Types.INTEGER);
		// include inactive government
		cStatement.setBoolean(7, paging.getInActive());
		// execute query
		cStatement.execute();
		// declare a result set object
		ResultSet result = null;
		while (result == null) {
			// get result
			cStatement.getMoreResults();
			result = cStatement.getResultSet();
		}
		while (result.next()) {
			// get data from result set
			// declare governmentVO object to store data of a government office
			// region
			GovernmentVO government = new GovernmentVO();
			// set government office region id
			government.setGovOfficeRegionID(result.getInt("GovOfficeRegionID"));
			// set government office region office name
			government.setGovOfficeRegionName(result
					.getString("GovOfficeRegionName"));
			// set description
			government.setDescription(result.getString("Description"));
			// set county id
			government.setCountyID(result.getInt("CountyID"));
			// set county name
			government.setCountyName(result.getString("CountyName"));
			// set status
			if (result.getBoolean("IsActive") == true) {
				government.setStatus("Yes");
			} else {
				government.setStatus("No");
			}

			// add governmentVO object to governmentVO
			governmentList.add(government);
		}
		// set number of page
		paging.setPageCount(cStatement.getInt(6));
		// close connection
		result.close();
		cStatement.close();
		connect.close();
		return governmentList;

	}

	/**
	 * getGovernmentInfo() method establish a connection then execute SQL query
	 * command to get data from DataBase then it return data contained in
	 * GovernmentVO class
	 * 
	 * @param idGOR ID of Government Office Region to get info
	 */
	public GovernmentVO getGovernmentInfo(int idGOR) throws Exception {
		/* Declare an object to store data */
		GovernmentVO gorInfo = new GovernmentVO();
		/* Declare Connection object */
		Connection connect = ConnectionFactory.getConnect();
		/* Create query string */
		String query = "{call sp_GetGORbyID(?)}";
		/* Declare Statement object */
		CallableStatement cStatement = connect.prepareCall(query);
		cStatement.setInt(1, idGOR);
		/* Declare ResultSet object */
		ResultSet result = cStatement.executeQuery();
		/* Get Info of Government Office Region */
		while (result.next()) {
			gorInfo.setGovOfficeRegionName(result
					.getString("GovOfficeRegionName"));
			gorInfo.setCountyName(result.getString("CountyName"));
			gorInfo.setDescription(result.getString("Description"));
		}
		/* Close DataBase */
		result.close();
		cStatement.close();
		connect.close();
		/* return Government Office Region Info */
		return gorInfo;
	}
}
